﻿using System;
using System.Collections.Generic;
using Excel = Microsoft.Office.Interop.Excel;

namespace QAToolsAddin {
    class CellsOperator {
        public delegate void CellWriter(ref object cell);
        public delegate void TableReader(object cell, bool isFirstCol, bool isLastCol);
        public delegate void CellReader(object cell);

        class Area {
            public Excel.Range Range;
            public object[,] Data;
            public bool[] HiddenRows;
            public bool[] HiddenCols;
            public int NbRow;
            public int NbCol;
        }

        private readonly List<Area> _areas;
        private readonly bool _withNullCells;
        private int _nbCells = 0;

        public int CellsCount { get { return _nbCells; } }

        public CellsOperator(Excel.Range range, bool withNullCells = false, Content contentType = Content.Value) {
            if (range == null) return;
            _withNullCells = withNullCells;
            bool fullCol = range.EntireColumn.Address == range.Address;
            if (fullCol && range.Worksheet.AutoFilter != null) {
                var filterRange = range.Worksheet.AutoFilter.Range;
                var intersect = range.Application.Intersect(range, filterRange.Offset[1, Type.Missing].Resize[filterRange.Rows.Count - 1, Type.Missing]);
                if (intersect != null)
                    range = intersect;
            } else if (withNullCells == false)
                range = range.Application.Intersect(range, range.Worksheet.UsedRange);

            if (range == null)
                return;

            _areas = new List<Area>(range.Areas.Count);
            _nbCells = 0;
            foreach (Excel.Range rgarea in range.Areas) {
                var area = rgarea;
                if (area.Worksheet.Cells.Address == area.Address)
                    area = rgarea.Resize[1000, 100];
                if (area.EntireColumn.Address == area.Address)
                    area = rgarea.Resize[1000, Type.Missing];
                else if (area.EntireRow.Address == area.Address)
                    area = rgarea.Resize[Type.Missing, 100];

                var data = ExcelArray<object>.Get(area, contentType);

                var nbrow = data.GetLength(0);
                var nbcol = data.GetLength(1);
                var hiddenrows = new bool[nbrow + 1];
                var hiddencols = new bool[nbcol + 1];

                _nbCells += nbrow * nbcol;

                if (area.EntireRow.RowHeight is System.DBNull)
                    for (int r = 1; r <= nbrow; r++) {
                        if ((bool)((Excel.Range)area.Rows[r]).Hidden) {
                            hiddenrows[r] = true;
                            _nbCells -= nbcol;
                        }
                    }
                if (area.EntireColumn.ColumnWidth is System.DBNull)
                    for (int c = 1; c <= nbcol; c++) {
                        if ((bool)((Excel.Range)area.Columns[c]).Hidden) {
                            hiddencols[c] = true;
                            _nbCells -= nbrow;
                        }

                    }
                _areas.Add(new Area {
                    Data = data,
                    NbRow = nbrow, NbCol = nbcol,
                    HiddenRows = hiddenrows, HiddenCols = hiddencols,
                    Range = area
                });
            }
        }

        internal void ForEachValue(string newNumberFormat, CellWriter cellDelegate) {
            if (_areas == null)
                return;
            foreach (var area in _areas) {
                Undo.AddUndoArea(area.Range);
                for (var r = 1; r <= area.NbRow; r++) {
                    if (area.HiddenRows[r]) continue;
                    for (var c = 1; c <= area.NbCol; c++) {
                        if (area.HiddenCols[c] || (!_withNullCells && area.Data[r, c] == null)) continue;
                        try {
                            cellDelegate(ref area.Data[r, c]);
                        } catch (Exception ex) {
                            ex.Data.Add(ex.Data.Count.ToString(), area.Data[r, c] == null ? null : "CellType: " + area.Data[r, c].GetType().ToString());
                            ex.Data.Add(ex.Data.Count.ToString(), area.Data[r, c] == null ? null : "CellValue: " + area.Data[r, c].ToString());
                            ex.Data.Add(ex.Data.Count.ToString(), "NumberFormat: " + newNumberFormat);
                            throw;
                        }
                    }
                }
                if (newNumberFormat != null && (area.Range.NumberFormat is System.DBNull || (string)area.Range.NumberFormat != newNumberFormat))
                    area.Range.NumberFormat = newNumberFormat;
                area.Range.Value = area.Data;
            }
        }

        internal void ForEachValue(TableReader cellDelegate) {
            if (_areas == null) return;
            foreach (var area in _areas) {
                int c = 0, r = 0;
                var nbrow = area.NbRow;
                var nbcol = area.NbCol;
                try {
                    for (r = 1; r <= nbrow; r++) {
                        if (area.HiddenRows[r]) continue;
                        for (c = 1; c <= nbcol; c++) {
                            if (area.HiddenCols[c] || (!_withNullCells && area.Data[r, c] == null)) continue;
                            cellDelegate(area.Data[r, c], c == 1, c == nbcol);
                        }
                    }
                } catch (Exception ex) {
                    ex.Data.Add(ex.Data.Count.ToString(), area.Data[r, c] == null ? null : "CellType: " + area.Data[r, c].GetType().ToString());
                    ex.Data.Add(ex.Data.Count.ToString(), area.Data[r, c] == null ? null : "CellValue: " + area.Data[r, c].ToString());
                    throw;
                }
            }
        }

        internal string[,] GetPaddedStringValues(string[] headers) {
            if (_areas == null) return null;
            int maxcol = 0, maxrow = 0;
            foreach (var area in _areas) {
                maxrow += area.NbRow;
                maxcol = Math.Max(maxcol, area.NbCol);
            }
            bool hasHeaders = headers != null && headers.Length != 0;
            if (hasHeaders) {
                maxrow++;
                maxcol = Math.Max(maxcol, headers.Length);
            }
            var values = new string[maxrow, maxcol];
            int colCpt = 0, rowCpt = 0;

            foreach (var area in _areas) {
                int c = 0, r = 0;
                var nbrow = area.NbRow;
                var nbcol = area.NbCol;
                try {
                    for (c = 1; c <= nbcol; c++) {
                        if (area.HiddenCols[c]) continue;
                        rowCpt = 0;
                        int maxlen = 0;
                        for (r = 1; r <= nbrow; r++) {
                            if (area.HiddenRows[r] || (!_withNullCells && area.Data[r, c] == null)) continue;
                            maxlen = Math.Max(area.Data[r, c].ToString().Length, maxlen);
                        }
                        if (area.Data[1, c] is string)
                            maxlen += 2;
                        if (hasHeaders) {
                            maxlen = Math.Max(headers[c - 1].Length, maxlen);
                            values[rowCpt++, colCpt] = headers[c - 1].PadRight(maxlen, ' ');
                        }
                        for (r = 1; r <= nbrow; r++) {
                            if (area.HiddenRows[r] || (!_withNullCells && area.Data[r, c] == null)) continue;
                            if (area.Data[r, c] is string)
                                values[rowCpt++, colCpt] = ('"' + (string)area.Data[r, c] + '"').PadRight(maxlen, ' ') ;
                            else
                                values[rowCpt++, colCpt] = area.Data[r, c].ToString().PadRight(maxlen, ' ');
                        }
                        colCpt++;
                    }
                } catch (Exception ex) {
                    ex.Data.Add(ex.Data.Count.ToString(), area.Data[r, c] == null ? null : "CellType: " + area.Data[r, c].GetType().ToString());
                    ex.Data.Add(ex.Data.Count.ToString(), area.Data[r, c] == null ? null : "CellValue: " + area.Data[r, c].ToString());
                    throw;
                }
            }
            return values;
        }

        internal void ForEachValue(CellReader cellDelegate) {
            if (_areas == null) return;
            foreach (Area area in _areas) {
                int c = 0, r = 0;
                var nbrow = area.NbRow;
                var nbcol = area.NbCol;
                try {
                    for (r = 1; r <= nbrow; r++) {
                        if (area.HiddenRows[r]) continue;
                        for (c = 1; c <= nbcol; c++) {
                            if (area.HiddenCols[c] || (!_withNullCells && area.Data[r, c] == null)) continue;
                            cellDelegate(area.Data[r, c]);
                        }
                    }
                } catch (Exception ex) {
                    ex.Data.Add(ex.Data.Count.ToString(), area.Data[r, c] == null ? null : "CellType: " + area.Data[r, c].GetType().ToString());
                    ex.Data.Add(ex.Data.Count.ToString(), area.Data[r, c] == null ? null : "CellValue: " + area.Data[r, c].ToString());
                    throw;
                }
            }
        }

        internal double[] GetDoubles() {
            if (_areas == null) return new double[] { };
            int cellCpt = 0;
            var values = new double[this.CellsCount];
            foreach (Area area in _areas) {
                int c = 0, r = 0;
                var nbrow = area.NbRow;
                var nbcol = area.NbCol;
                try {
                    for (r = 1; r <= nbrow; r++) {
                        if (area.HiddenRows[r]) continue;
                        for (c = 1; c <= nbcol; c++) {
                            if (area.HiddenCols[c] || (!_withNullCells && area.Data[r, c] == null)) continue;
                            if (area.Data[r, c] is double)
                                values[cellCpt++] = (double)area.Data[r, c];
                        }
                    }
                } catch (Exception ex) {
                    ex.Data.Add(ex.Data.Count.ToString(), area.Data[r, c] == null ? null : "CellType: " + area.Data[r, c].GetType().ToString());
                    ex.Data.Add(ex.Data.Count.ToString(), area.Data[r, c] == null ? null : "CellValue: " + area.Data[r, c].ToString());
                    throw;
                }
            }
            Array.Resize<double>(ref values, cellCpt);
            return values;
        }

        internal object[] GetValues() {
            if (_areas == null) return new object[] { };
            int cellCpt = 0;
            var values = new object[this.CellsCount];
            foreach (Area area in _areas) {
                int c = 0, r = 0;
                var nbrow = area.NbRow;
                var nbcol = area.NbCol;
                try {
                    for (r = 1; r <= nbrow; r++) {
                        if (area.HiddenRows[r]) continue;
                        for (c = 1; c <= nbcol; c++) {
                            if (area.HiddenCols[c] || (!_withNullCells && area.Data[r, c] == null)) continue;
                            values[cellCpt++] = area.Data[r, c];
                        }
                    }
                } catch (Exception ex) {
                    ex.Data.Add(ex.Data.Count.ToString(), area.Data[r, c] == null ? null : "CellType: " + area.Data[r, c].GetType().ToString());
                    ex.Data.Add(ex.Data.Count.ToString(), area.Data[r, c] == null ? null : "CellValue: " + area.Data[r, c].ToString());
                    throw;
                }
            }
            Array.Resize<object>(ref values, cellCpt);
            return values;
        }

        internal string[] GetStrings() {
            if (_areas == null) return new string[] { };
            int cellCpt = 0;
            var values = new string[this.CellsCount];
            foreach (Area area in _areas) {
                int c = 0, r = 0;
                var nbrow = area.NbRow;
                var nbcol = area.NbCol;
                try {
                    for (r = 1; r <= nbrow; r++) {
                        if (area.HiddenRows[r]) continue;
                        for (c = 1; c <= nbcol; c++) {
                            if (area.HiddenCols[c] || (!_withNullCells && area.Data[r, c] == null)) continue;
                            values[cellCpt++] = area.Data[r, c].ToString();
                        }
                    }
                } catch (Exception ex) {
                    ex.Data.Add(ex.Data.Count.ToString(), area.Data[r, c] == null ? null : "CellType: " + area.Data[r, c].GetType().ToString());
                    ex.Data.Add(ex.Data.Count.ToString(), area.Data[r, c] == null ? null : "CellValue: " + area.Data[r, c].ToString());
                    throw;
                }
            }
            Array.Resize<string>(ref values, cellCpt);
            return values;
        }

        internal static void AddDataToRange(Excel.Range range, int nbrow, string numberFormat, CellWriter cellDelegate) {
            var array = ExcelArray<object>.Create(nbrow, 1);
            for (int r = 1; r <= nbrow; r++)
                cellDelegate(ref array[r, 1]);
            CellsOperator.AddDataToRange(range, array, numberFormat);
        }

        internal static void AddDataToRange(Excel.Range range, object data, string numberFormat = null) {
            int nbRow, nbCol;
            if (data is object[,]) {
                nbRow = ((object[,])data).GetLength(0);
                nbCol = ((object[,])data).GetLength(1);
            } else if (data is double[,]) {
                nbRow = ((double[,])data).GetLength(0);
                nbCol = ((double[,])data).GetLength(1);
            } else {
                throw new ArgumentException("Unexpected argument type");
            }
            if (nbRow == 0 || nbCol == 0) return;
            int nbRowMax = Math.Max(nbRow, range.Rows.Count);
            int nbMaxCol = Math.Max(nbCol, range.Columns.Count);
            var maxRange = range.Resize[nbRowMax, nbMaxCol];
            Undo.AddUndoArea(maxRange);
            maxRange.Clear();
            Excel.Range newRange = range.Resize[nbRow, nbCol];
            if (numberFormat != null)
                newRange.NumberFormat = numberFormat;
            newRange.Value = data;
        }

        internal int CountVisibleColumns() {
            int nbVisibleColumn = 0;
            if (_areas != null)
                foreach (var area in _areas) {
                    for (var c = 1; c <= area.NbCol; c++) {
                        if (area.HiddenCols[c]) continue;
                        nbVisibleColumn++;
                    }
                }
            return nbVisibleColumn;
        }

    }
}
